package com.lkedu.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;

import com.lkedu.entity.CourseBean;
import com.lkedu.entity.UserBean;
import com.lkedu.utils.DataSourceUtils;


public class UserDao {

	QueryRunner runner=new QueryRunner(DataSourceUtils.getDataSource());
	//插入用户
	public int insert(UserBean user) {
		String sql="insert into tb_user values(?,?,?,?,?,?,?,?)";
		Object[] params= {user.getUid(),user.getUsername(),user.getPassword(),user.getPhone(),
				user.getEmail(),user.getSex(),user.getBirthday(),user.getState()};
		System.out.println(params);
		int result=-1;
		try {
			result=runner.update(sql,params);			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
	
	//更新用户信息
	public int update(UserBean user) {
		String sql="update tb_user set username=?,password=?,phone=?,email=?,sex=? where uid=?";
		Object[] params= {user.getUsername(),user.getPassword(),user.getPhone(),
				user.getEmail(),user.getSex(),user.getUid()};
		int result=-1;
		try {
			result=runner.update(sql,params);
			return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
	
	//修改密码
	public int update(String password,String uid) {
		String sql="update tb_user set password=? where uid=?";
		Object[] params= {password,uid};
		int result=-1;
		try {
			result=runner.update(sql,params);
			return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
	
	//修改用户状态
	public int update(int state,String uid) {
		String sql="update tb_user set state=? where uid=?";
		Object[] params= {state,uid};
		int result=-1;
		try {
			result=runner.update(sql,params);
			return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
	
	//删除用户
	public int delete(String uid) {
		String sql="delete from tb_user where uid=?";
		Object[] params= {uid};
		int result=-1;
		try {
			result=runner.update(sql,params);
			return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
	
	
	
	//uid查询用户
	public UserBean get(String uid) {
		//QueryRunner可以将查询的结果转化位对应的Bean对象
		String sql="select * from tb_user where uid=?";
		Object[] params= {uid};
		//参数ResultSetHandler接口--接收实现该接口的对象
		//	--BeanHandler    将查询的结果转化为对应对象
		UserBean u=null;
		try {
			u=runner.query(sql, new BeanHandler<UserBean>(UserBean.class), params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return u;
	}
	
	//username查询用户
	public UserBean getByUserName(String username) {
		String sql="select * from tb_user where username=?";
		Object[] params= {username};
		UserBean u=null;
		try {
			u=runner.query(sql, new BeanHandler<UserBean>(UserBean.class), params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return u;
	}

	public UserBean checkUsernameAndEmail(String username, String email) {
		String sql="select * from tb_user where username=? and email=?";
		Object[] params= {username,email};
		UserBean u=null;
		try {
			u=runner.query(sql, new BeanHandler<UserBean>(UserBean.class), params);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return u;
	}

	public int changePwwd(String uid, String newPwd) {
		String sql="update tb_user set password=? where uid=?";
		Object[] params= {newPwd,uid};
		int result=-1;
		try {
			result=runner.update(sql,params);
			return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
		
	}

	public List<CourseBean> myCourse(String uname) {
		String sql="SELECT tb_course.* FROM tb_course,user_course WHERE user_course.uname=? AND user_course.cid=tb_course.cid";
		Object[] params= {uname};
		List<CourseBean> cs=null;
		try {
			cs=runner.query(sql, new BeanListHandler<CourseBean>(CourseBean.class),params);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return cs;
	}

	public int delCourse(String uname, String cid) {
		String sql="DELETE  FROM user_course WHERE uname=? AND cid=?";
		Object[] params= {uname,cid};
		int result=-1;
		try {
			result=runner.update(sql,params);
			return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}

	public int addCourse(String uname, String cid) {
		String sql="INSERT  INTO user_course VALUE(?,?)";
		Object[] params= {uname,cid};
		int result=-1;
		try {
			result=runner.update(sql,params);
			return result;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return result;
	}
}
